import pandas as pd
import os

def load_translation_table( filename, out ):
    df = pd.read_excel(io=filename)
    colums=df.columns
    rv=""
    prefix=os.path.basename(filename).split('.')[0]
    size_len_ser = 0
    size_len_cal = 0
    size_len_pro = 0
    
    add_ser = 0
    add_cal = 0
    max_index = 0
    add_product = 0
    value_min = 0
    reserver_offset = 0
    value_type = 0
    for i in df.index:
        line=""
        x=0
        seperator=""
        for column_name in colums:
            value=df.iloc[i,x]

            if type(value)==str:            
                if value.startswith('0x'): # Hex number as string
                     
                    out.write("{}{}={}".format(seperator, column_name, value))
                else:
                    value=str(value).replace('\"','')
                    if( value == 'nan'):
                        value=''
                    value=value.replace('\n',r'\n')
                     
                    
                    
                    if column_name=='prefix':
                        prefix=value 
                        
                        if prefix == 'calibration' and add_ser ==0:
                            add_ser = 1
                            size_len_ser = 0x800 - 28 - size_len_ser
                            for k in  range (0,size_len_ser>>2):
                                seperatorx=""
                                max_index+=1
                                valuex='service\''+ ','+'name=\'service:'+'reserve' +str(k) +'\',' +'type=\'paragen_type_u32_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'              
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                out.write(',version=0x0011\n') 
                            reserver_offset = k+1
                            for k in  range (0,size_len_ser%4):
                                seperatorx=""
                                max_index+=1
                                valuex='service\''+ ','+'name=\'service:'+'reserve' +str(k+reserver_offset) +'\',' +'type=\'paragen_type_u8_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'                 
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                                                
                                out.write(',version=0x0011\n')
                            max_index = 0
                        if prefix == 'production' and add_cal == 0:
                            add_cal = 1
                            size_len_cal = 0x200  - 28- size_len_cal
                            for k in  range (0,size_len_cal>>2):
                                seperatorx="" 
                                max_index+=1
                                valuex='calibration\''+ ','+'name=\'calibration:'+'reserve' +str(k) +'\',' +'type=\'paragen_type_u32_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'   

                                
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                out.write(',version=0x0011\n')
                            reserver_offset = k+1                               
                            for k in  range (0,size_len_cal%4):
                                seperatorx=""
                                max_index+=1
                                valuex='calibration\''+ ','+'name=\'calibration:'+'reserve' +str(k+reserver_offset) +'\',' +'type=\'paragen_type_u8_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'             
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                out.write(',version=0x0011\n')
                            max_index = 0
                        if prefix == 'mon' and add_product == 0:
                            add_product = 1
                            size_len_pro = 0x40 - 28 - size_len_pro
                            for k in  range (0,size_len_pro>>2):
                                seperatorx="" 
                                max_index+=1
                                valuex='production\''+ ','+'name=\'production:'+'reserve' +str(k) +'\',' +'type=\'paragen_type_u32_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'   

                                
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                out.write(',version=0x0011\n') 
                            reserver_offset = k+1
                            for k in  range (0,size_len_pro%4):
                                seperatorx=""
                                max_index+=1
                                valuex='production\''+ ','+'name=\'production:'+'reserve' +str(k+reserver_offset) +'\',' +'type=\'paragen_type_u8_mm\''+ ','+'index='+str(max_index)+','+'default=0';
                                valuex+= ',min=0'
                                valuex+= ',max=0'
                                valuex+= ',read=0'
                                valuex+= ',write=0'
                                valuex+= ',increment=0'
                                valuex+= ',scale=0'
                                valuex+= ',offset=0'
                                valuex+= ',unit=\'[0.01V]\''
                                valuex+= ',comment=\'\''
                                valuex+= ',remark=\'\''
                                
                                column_namex='prefix'             
                                out.write("{}{}='{}".format(seperatorx, column_namex, valuex) ) 
                                out.write(',version=0x0011\n')
                            
                            
                           
                    if column_name=='name':
                        value=prefix+':'+value.lower()

                    if column_name=='type':
                        value= 'paragen_type_{}_mm'.format( value.lower() )     
                        if prefix == 'service':
                            if value == 'paragen_type_u8_mm' or value == 'paragen_type_s8_mm':
                                size_len_ser += 1
                            elif  value == 'paragen_type_u16_mm' or value == 'paragen_type_s16_mm':
                                size_len_ser += 2
                            elif  value == 'paragen_type_s32_mm' or value == 'paragen_type_u32_mm':
                                size_len_ser += 4
                        elif prefix == 'calibration':
                            if value == 'paragen_type_u8_mm' or value == 'paragen_type_s8_mm':
                                size_len_cal += 1
                            elif  value == 'paragen_type_u16_mm' or value == 'paragen_type_s16_mm':
                                size_len_cal += 2
                            elif  value == 'paragen_type_s32_mm' or value == 'paragen_type_u32_mm':
                                size_len_cal += 4
                        elif prefix == 'production':
                            if value == 'paragen_type_u8_mm' or value == 'paragen_type_s8_mm':
                                size_len_pro += 1
                            elif  value == 'paragen_type_u16_mm' or value == 'paragen_type_s16_mm':
                                size_len_pro += 2
                            elif  value == 'paragen_type_s32_mm' or value == 'paragen_type_u32_mm':
                                size_len_pro += 4
                            elif  value == 'paragen_type_ch_mm':
                                value_type = 'paragen_type_ch_mm'
                                                    
                    out.write("{}{}='{}'".format(seperator, column_name, value) )                
            else:
                if str(value)=='nan':
                    out.write("{}{}=''".format(seperator, column_name ))            
                else:
                    if column_name=='min':
                        if  value_type == 'paragen_type_ch_mm':
                            if prefix == 'production':
                                size_len_pro += int(value)
                            value_type = 0
                    if column_name=='index':
                        if int(value) > max_index:
                            max_index = int(value) 
                    out.write("{}{}={}".format(seperator, column_name, int(value) ))

            seperator=","
            x+=1
        out.write(',version=0x0011\n')
        

    return None

if __name__ =='__main__':
    import sys
    
    load_translation_table(sys.argv[1], sys.stdout )

